﻿use ASPNetDB
GO

/****** Object:  Table [dbo].[CompanyUser]    Script Date: 06/03/2010 09:33:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CompanyUser](
	[CustomerUserId] [int] NOT NULL,
	[CustomerId] [int] NOT NULL,
	[UserId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_CompanyUser] PRIMARY KEY CLUSTERED 
(
	[CustomerUserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[CompanyUser]  WITH CHECK ADD  CONSTRAINT [FK_CompanyUser_aspnet_Users] FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO

ALTER TABLE [dbo].[CompanyUser] CHECK CONSTRAINT [FK_CompanyUser_aspnet_Users]
GO

ALTER TABLE [dbo].[CompanyUser]  WITH CHECK ADD  CONSTRAINT [FK_CompanyUser_Customer] FOREIGN KEY([CustomerId])
REFERENCES [dbo].[Customer] ([id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[CompanyUser] CHECK CONSTRAINT [FK_CompanyUser_Customer]
GO


/****** Object:  Table [dbo].[Customer]    Script Date: 06/03/2010 09:33:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Customer](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[customerName] [varchar](100) NOT NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/****** Object:  Table [dbo].[Employee]    Script Date: 06/03/2010 09:34:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Employee](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[firstName] [varchar](50) NOT NULL,
	[lastName] [varchar](50) NOT NULL,
	[emailAddress] [varchar](400) NOT NULL,
	[userID] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_aspnet_Users] FOREIGN KEY([userID])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO

ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_aspnet_Users]
GO

/****** Object:  Table [dbo].[HostInformation]    Script Date: 06/03/2010 09:35:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[HostInformation](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](500) NOT NULL,
	[City] [nvarchar](200) NOT NULL,
	[State] [nvarchar](4) NOT NULL,
	[contactEmail] [nvarchar](500) NOT NULL,
	[phone] [nvarchar](20) NOT NULL,
	[fax] [nvarchar](20) NOT NULL,
	[Address] [nvarchar](500) NOT NULL,
	[Zip] [nvarchar](15) NOT NULL,
 CONSTRAINT [PK_HostInformation] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[Project]    Script Date: 06/03/2010 09:35:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Project](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](150) NOT NULL,
	[customerID] [int] NOT NULL,
	[startDate] [datetime] NULL,
	[endDate] [datetime] NULL,
	[timeTotal] [decimal](6, 1) NOT NULL,
 CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Project]  WITH CHECK ADD  CONSTRAINT [FK_Project_Customer] FOREIGN KEY([customerID])
REFERENCES [dbo].[Customer] ([id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Project] CHECK CONSTRAINT [FK_Project_Customer]
GO

ALTER TABLE [dbo].[Project] ADD  CONSTRAINT [DF_Project_timeTotal]  DEFAULT ((0)) FOR [timeTotal]
GO


/****** Object:  Table [dbo].[timeEntry]    Script Date: 06/03/2010 09:36:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[timeEntry](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[projectID] [int] NOT NULL,
	[timeInterval] [decimal](6, 1) NOT NULL,
	[timestamp] [smalldatetime] NOT NULL,
	[EmployeeID] [int] NOT NULL,
	[Description] [nvarchar](500) NULL,
 CONSTRAINT [PK_timeEntry] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[timeEntry]  WITH CHECK ADD  CONSTRAINT [FK_timeEntry_Employee] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employee] ([ID])
GO

ALTER TABLE [dbo].[timeEntry] CHECK CONSTRAINT [FK_timeEntry_Employee]
GO

ALTER TABLE [dbo].[timeEntry]  WITH CHECK ADD  CONSTRAINT [FK_timeEntry_Project] FOREIGN KEY([projectID])
REFERENCES [dbo].[Project] ([id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[timeEntry] CHECK CONSTRAINT [FK_timeEntry_Project]
GO

